﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BookManagermentSystem
{
    public partial class BookManagermentForm : Form
    {
        string connStr = "Server=127.0.0.1;Database=csharpbookdb;uid=root;pwd=123456;";

        public BookManagermentForm()
        {
            InitializeComponent();
        }

        private void btnBackMenu_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void BookManagermentForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            MainMenuForm.getInstance().Show();
        }

        private void DataBind()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    MySqlCommand comm = conn.CreateCommand();
                    comm.CommandText = "select bkID 书号, bkName 书名, bkAuthor 作者, bkPress 出版社, bkPrice 单价, bkStatus 状态 from book";
                    MySqlDataAdapter sda = new MySqlDataAdapter(comm);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    dgvBook.DataSource = ds.Tables[0];
                    tbBkID.DataBindings.Clear();
                    tbBkName.DataBindings.Clear();
                    tbBkAuthor.DataBindings.Clear();
                    tbBkPress.DataBindings.Clear();
                    tbBkPrice.DataBindings.Clear();
                    tbBkStatus.DataBindings.Clear();

                    tbBkID.DataBindings.Add("Text", ds.Tables[0], "书号");
                    tbBkName.DataBindings.Add("Text", ds.Tables[0], "书名");
                    tbBkAuthor.DataBindings.Add("Text", ds.Tables[0], "作者");
                    tbBkPress.DataBindings.Add("Text", ds.Tables[0], "出版社");
                    tbBkPrice.DataBindings.Add("Text", ds.Tables[0], "单价");
                    tbBkStatus.DataBindings.Add("Text", ds.Tables[0], "状态");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作数据库出错 " + ex.Message);
            }
        }

        private void BookManagermentForm_Load(object sender, EventArgs e)
        {
            DataBind();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    MySqlCommand comm = conn.CreateCommand();
                    comm.CommandText = "insert into book values(@bkID, @bkName, @bkAuthor, @bkPress, @bkPrice, @bkStatus)";
                    comm.Parameters.AddWithValue("@bkID", tbBkID.Text);
                    comm.Parameters.AddWithValue("@bkName", tbBkName.Text);
                    comm.Parameters.AddWithValue("@bkAuthor", tbBkAuthor.Text);
                    comm.Parameters.AddWithValue("@bkPress", tbBkPress.Text);
                    comm.Parameters.AddWithValue("@bkPrice", tbBkPrice.Text);
                    comm.Parameters.AddWithValue("@bkStatus", tbBkStatus.Text);

                    comm.ExecuteNonQuery();
                    MessageBox.Show("添加成功");
                    DataBind();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("添加失败 " + ex.Message);
            }
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    MySqlCommand comm = conn.CreateCommand();
                    comm.CommandText = "select bkID 书号, bkName 书名, bkAuthor 作者, bkPress 出版社, bkPrice 单价, bkStatus 状态 from book where bkName like CONCAT(@bkName, '%')";
                    comm.Parameters.AddWithValue("@bkName", tbBkName.Text);
                    MySqlDataAdapter sda = new MySqlDataAdapter(comm);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    dgvBook.DataSource = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作数据库出错 " + ex.Message);
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
            DialogResult dr = MessageBox.Show("确定要删除吗?", "确定", messButton);

            if (dr == DialogResult.OK)
            {
                try
                {
                    using (MySqlConnection conn = new MySqlConnection(connStr))
                    {
                        conn.Open();
                        MySqlCommand comm = conn.CreateCommand();
                        comm.CommandText = "delete from book where bkID = @bkID";
                        comm.Parameters.AddWithValue("@bkID", tbBkID.Text);
                        comm.ExecuteNonQuery();
                        MessageBox.Show("删除成功");
                        DataBind();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("删除失败 " + ex.Message);
                }
            }
        }

        private void btnAlter_Click(object sender, EventArgs e)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    MySqlCommand comm = conn.CreateCommand();
                    comm.CommandText = "update book set bkID = @bkID, bkName = @bkName, bkAuthor = @bkAuthor, bkPress = @bkPress, bkPrice = @bkPrice, bkStatus = @bkStatus where bkID = @bkID";
                    comm.Parameters.AddWithValue("@bkID", tbBkID.Text);
                    comm.Parameters.AddWithValue("@bkName", tbBkName.Text);
                    comm.Parameters.AddWithValue("@bkAuthor", tbBkAuthor.Text);
                    comm.Parameters.AddWithValue("@bkPress", tbBkPress.Text);
                    comm.Parameters.AddWithValue("@bkPrice", tbBkPrice.Text);
                    comm.Parameters.AddWithValue("@bkStatus", tbBkStatus.Text);
                    comm.ExecuteNonQuery();
                    MessageBox.Show("更新成功");
                    DataBind();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("修改失败 " + ex.Message);
            }
        }
    }
}
